package approve.dao;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import model.pojo.Employee;
import models.util.MySqlDataAccessHelper;

public class ListReferenceDAO {
	public static boolean update(String id,String group,String db){
		int a = 0;
		MySqlDataAccessHelper mysql  = new MySqlDataAccessHelper();
		String sql = "CALL sp_updateReference(?,?)";
		CallableStatement cs = null;
		try {
			mysql.open(db);
			cs = mysql.getConnection().prepareCall(sql);
			cs.setString(1,id);
			cs.setString(2, group);
			a = cs.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
			try {
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return a>0;
	}
	
	public static boolean updateaccept(String id,String group,MySqlDataAccessHelper mysql){
		int a = 0;
		String sql = "UPDATE list_reference p SET p.id_status = 2 WHERE p.id_manager='"+id+"' AND p.id_group='"+group+"'";
		try {
			mysql.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return a>0;
	}
	
	public static ArrayList<Employee> getReferenceByIDEmployee(String id,String group,String db){
		ArrayList<Employee> lst = new ArrayList<Employee>();
		MySqlDataAccessHelper mysql  = new MySqlDataAccessHelper();
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "select l.id_employee,e.FullName from list_reference l inner join employee e on l.id_employee=e.IDEmployee where l.id_manager=? and l.id_group=?";
		try {
			mysql.open(db);
			ps = mysql.getConnection().prepareStatement(sql);
			ps.setString(1, id);
			ps.setString(2, group);
			rs = ps.executeQuery();
			while(rs.next()){
				lst.add(new Employee(rs.getString("l.id_employee"), rs.getString("e.FullName"),true));
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return lst;
	}
	
	public static boolean delete(String id,String group,String db){
		int a = 0;
		MySqlDataAccessHelper mysql  = new MySqlDataAccessHelper();
		String sql = "delete from list_reference where id_manager = '"+id+"' and id_group = '"+group+"'";
		try {
			mysql.open(db);			
			a = mysql.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return a>0;
	}
	
	public static boolean insert(String sql,String db){
		int a = 0;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		try {
			mysql.open(db);
			a=mysql.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		} finally{
			mysql.close();
		}
		return a>0;
	}
	
	public static boolean acceptReference(String group, String emp,String db){
		int i = 0;
		MySqlDataAccessHelper mysql = new MySqlDataAccessHelper();
		String sql ="update list_reference set id_status = 1 WHERE id_group='"+group+"' AND id_employee='"+emp+"'";
		try {
			mysql.open(db);
			i = mysql.executeUpdate(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			mysql.close();
		}
		return i >0;
	}
}